package org.jeecg.modules.demo.crm.util.execl;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.modules.demo.crm.entity.CQuotationInfo;
import org.jeecg.modules.demo.task.ExcelReportGenerator;

import java.io.*;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class ExcelReportGeneratorUtils {

    // 下载远程Excel模板
    public static File downloadTemplate(String fileUrl, String savePath) throws IOException {
        URL url = new URL(fileUrl);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setConnectTimeout(10000);
        conn.setReadTimeout(10000);
        conn.connect();

        try (InputStream in = conn.getInputStream();
             FileOutputStream out = new FileOutputStream(savePath)) {
            byte[] buffer = new byte[4096];
            int len;
            while ((len = in.read(buffer)) != -1) {
                out.write(buffer, 0, len);
            }
        }
        return new File(savePath);
    }


    // 生成报价单
    public static void generateExcel(File templateFile, String outputPath, String projectName,
                                     String projectNo,
                                     String remark,
                                     String partyA,
                                     Date partyADate,
                                     String partyB,
                                     Date partyBDate,
                                     List<CQuotationInfo> items) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        try (FileInputStream fis = new FileInputStream(templateFile);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);


            // === 固定位置写入 ===
            // 例如 名称在第4行第3列，项目编号在第4行第8列 （具体根据模板调整）
            Row row0 = sheet.getRow(1);
            row0.getCell(1).setCellValue(projectName);
            row0.getCell(5).setCellValue(projectNo);

            // === 明细写入 ===
            int startRow = 3; // 明细开始行（模板里从第6行开始，2基准）
            int rowIndex = startRow;
            int colIndex = 1;
            for (CQuotationInfo item : items) {
                Row row = sheet.createRow(rowIndex++);
                row.createCell(0).setCellValue(colIndex);
                row.createCell(1).setCellValue(item.getName());
                row.createCell(2).setCellValue(item.getQuantity()==null?BigDecimal.ZERO+"":item.getQuantity().toString());
                row.createCell(3).setCellValue(item.getUnit());
                row.createCell(4).setCellValue(item.getMainMaterial()==null?BigDecimal.ZERO+"":item.getMainMaterial().toString());
                row.createCell(5).setCellValue(item.getAuxMaterial()==null?BigDecimal.ZERO+"":item.getAuxMaterial().toString());
                row.createCell(6).setCellValue(item.getLabor()==null?BigDecimal.ZERO+"":item.getLabor().toString());
                row.createCell(7).setCellValue(item.getTotal()==null?BigDecimal.ZERO+"":item.getTotal().toString());
                row.createCell(8).setCellValue(item.getRemarks());
                colIndex++;
            }

            // === 小计 ===
            Row subtotalRow = sheet.createRow(rowIndex++);
            subtotalRow.createCell(0).setCellValue("小计(不含税)");//3列
            BigDecimal sum = items.stream()
                    .map(CQuotationInfo::getTotal)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            subtotalRow.createCell(2).setCellValue(sum.toString());//第9列

            //// 合并  文字单元格
            CellRangeAddress subtotalRegion11 = new CellRangeAddress(
                    subtotalRow.getRowNum(), // 起始行
                    subtotalRow.getRowNum(), // 结束行（同一行）
                    0,                       // 起始列
                    1                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion11);
            CellRangeAddress subtotalRegion = new CellRangeAddress(
                    subtotalRow.getRowNum(), // 起始行
                    subtotalRow.getRowNum(), // 结束行（同一行）
                    2,                       // 起始列
                    8                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion);


            // === 备注 ===
            Row remarkRow = sheet.createRow(rowIndex++);
            remarkRow.createCell(0).setCellValue("备注:");
            remarkRow.createCell(2).setCellValue(remark);
            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion12 = new CellRangeAddress(
                    remarkRow.getRowNum(), // 起始行
                    remarkRow.getRowNum(), // 结束行（同一行）
                    0,                       // 起始列
                    1                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion12);
            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion1 = new CellRangeAddress(
                    remarkRow.getRowNum(), // 起始行
                    remarkRow.getRowNum(), // 结束行（同一行）
                    2,                       // 起始列
                    8                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion1);



            Row partyARow2 = sheet.createRow(rowIndex);
            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion2 = new CellRangeAddress(
                    partyARow2.getRowNum(), // 起始行
                    partyARow2.getRowNum(), // 结束行（同一行）
                    0,                       // 起始列
                    8                        // 结束列
            );
           sheet.addMergedRegion(subtotalRegion2);

            // === 签字栏 ===
            rowIndex += 1;
            int noBorderIndex = rowIndex;
            Row partyARow = sheet.createRow(rowIndex++);
            partyARow.createCell(0).setCellValue("甲方:");
            partyARow.createCell(2).setCellValue(partyA);

            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion3 = new CellRangeAddress(
                    partyARow.getRowNum(), // 起始行
                    partyARow.getRowNum(), // 结束行（同一行）
                    0,                       // 起始列
                    1                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion3);
            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion13 = new CellRangeAddress(
                    partyARow.getRowNum(), // 起始行
                    partyARow.getRowNum(), // 结束行（同一行）
                    2,                       // 起始列
                    4                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion13);


            partyARow.createCell(5).setCellValue("乙方:");
            partyARow.createCell(7).setCellValue(partyB);
            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion4 = new CellRangeAddress(
                    partyARow.getRowNum(), // 起始行
                    partyARow.getRowNum(), // 结束行（同一行）
                    5,                       // 起始列
                    6                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion4);
            CellRangeAddress subtotalRegion41 = new CellRangeAddress(
                    partyARow.getRowNum(), // 起始行
                    partyARow.getRowNum(), // 结束行（同一行）
                    7,                       // 起始列
                    8                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion41);



            Row partyBRow = sheet.createRow(rowIndex++);
            partyBRow.createCell(0).setCellValue("日期:");
            if(partyADate != null){
                partyBRow.createCell(2).setCellValue(sdf.format(partyADate));
            }else {
                partyBRow.createCell(2).setCellValue("");
            }

            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion51 = new CellRangeAddress(
                    partyBRow.getRowNum(), // 起始行
                    partyBRow.getRowNum(), // 结束行（同一行）
                    0,                       // 起始列
                    1                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion51);
            CellRangeAddress subtotalRegion5 = new CellRangeAddress(
                    partyBRow.getRowNum(), // 起始行
                    partyBRow.getRowNum(), // 结束行（同一行）
                    2,                       // 起始列
                    4                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion5);



            partyBRow.createCell(5).setCellValue("日期:");
            if(partyBDate != null){
                partyBRow.createCell(7).setCellValue(sdf.format(partyBDate));
            }else {
                partyBRow.createCell(7).setCellValue("");
            }

            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion61 = new CellRangeAddress(
                    partyBRow.getRowNum(), // 起始行
                    partyBRow.getRowNum(), // 结束行（同一行）
                    5,                       // 起始列
                    6                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion61);
            CellRangeAddress subtotalRegion6 = new CellRangeAddress(
                    partyBRow.getRowNum(), // 起始行
                    partyBRow.getRowNum(), // 结束行（同一行）
                    7,                       // 起始列
                    8                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion6);

            // === 整个区域加田字格边框 ===
            int startCellRow = 0;        // 明细开始行
            int endCellRow = rowIndex-1; // 明细 + 小计 + 备注 + 签字栏的最后一行
            int startCol = 0;
            int endCol = 8;



            // 遍历区域内的每个单元格，应用样式
            for (int r = startCellRow; r <= endCellRow; r++) {
                Row row = sheet.getRow(r);
                if (row == null) row = sheet.createRow(r);
                for (int c = startCol; c <= endCol; c++) {
                    // 定义一个带四边边框的样式
                    CellStyle borderStyle = workbook.createCellStyle();
                    if(c==8){
                        borderStyle.setBorderRight(BorderStyle.MEDIUM);
                    }else {
                        borderStyle.setBorderRight(BorderStyle.DOTTED);
                    }
                    if(r == 0){
                        borderStyle.setBorderTop(BorderStyle.MEDIUM);
                    }else {
                        borderStyle.setBorderTop(BorderStyle.DOTTED);
                    }
                    if(c==0){
                        borderStyle.setBorderLeft(BorderStyle.MEDIUM);
                    }else {
                        borderStyle.setBorderLeft(BorderStyle.DOTTED);
                    }

                    borderStyle.setBorderBottom(BorderStyle.DOTTED);

                    borderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    borderStyle.setAlignment(HorizontalAlignment.CENTER);

                    Cell cell = row.getCell(c);
                    if (cell == null) cell = row.createCell(c);
                    cell.setCellStyle(borderStyle);
                }
                if (r==0){
                    row.setHeightInPoints(60);//行高设置30磅
                }else {
                    row.setHeightInPoints(30);//行高设置30磅
                }

            }


            /* ---- 第6行(索引7)只去边框，保留内容 ---- */
            Row row7 = sheet.getRow(rowIndex-3);
            if (row7 == null) row7 = sheet.createRow(rowIndex-3);

            CellStyle nb7 = workbook.createCellStyle();   // 新样式
            nb7.cloneStyleFrom(row7.getCell(0) == null ? workbook.createCellStyle()
                    : row7.getCell(0).getCellStyle()); // 继承原样
            nb7.setBorderTop(BorderStyle.MEDIUM);
            nb7.setBorderBottom(BorderStyle.NONE);
            nb7.setBorderLeft(BorderStyle.NONE);
            nb7.setBorderRight(BorderStyle.NONE);

            for (int c = 0; c <= 8; c++) {
                Cell cell = row7.getCell(c);
                if (cell == null) cell = row7.createCell(c);
                cell.setCellStyle(nb7);   // 只换边框，不动内容
            }


            /* ---- 第8行(索引7)只去边框，保留内容 ---- */
            Row row8 = sheet.getRow(rowIndex-2);
            if (row8 == null) row8 = sheet.createRow(rowIndex-2);

            CellStyle nb = workbook.createCellStyle();   // 新样式
            nb.cloneStyleFrom(row8.getCell(0) == null ? workbook.createCellStyle()
                    : row8.getCell(0).getCellStyle()); // 继承原样
            nb.setBorderTop(BorderStyle.NONE);
            nb.setBorderBottom(BorderStyle.NONE);
            nb.setBorderLeft(BorderStyle.NONE);
            nb.setBorderRight(BorderStyle.NONE);

            for (int c = 0; c <= 8; c++) {
                Cell cell = row8.getCell(c);
                if (cell == null) cell = row8.createCell(c);
                cell.setCellStyle(nb);   // 只换边框，不动内容
            }

            /* ---- 第8行(索引7)只去边框，保留内容 ---- */
            Row row9 = sheet.getRow(rowIndex-1);
            if (row9 == null) row9 = sheet.createRow(rowIndex-1);

            CellStyle nb9 = workbook.createCellStyle();   // 新样式
            nb9.cloneStyleFrom(row9.getCell(0) == null ? workbook.createCellStyle(): row9.getCell(0).getCellStyle()); // 继承原样
            nb9.setBorderTop(BorderStyle.NONE);
            nb9.setBorderBottom(BorderStyle.NONE);
            nb9.setBorderLeft(BorderStyle.NONE);
            nb9.setBorderRight(BorderStyle.NONE);

            for (int c = 0; c <= 8; c++) {
                Cell cell = row9.getCell(c);
                if (cell == null) cell = row9.createCell(c);
                cell.setCellStyle(nb9);   // 只换边框，不动内容
            }



            // === 输出 ===
            try (FileOutputStream fos = new FileOutputStream(outputPath)) {
                workbook.write(fos);
            }
        }
    }

}
